# -*- encoding: utf-8 -*-
##############################################################################
#
#    OpenERP, Open Source Management Solution
#    Copyright (C) 2004-2009 Tiny SPRL (<http://tiny.be>). All Rights Reserved
#    $Id$
#
#    This program is free software: you can redistribute it and/or modify
#    it under the terms of the GNU General Public License as published by
#    the Free Software Foundation, either version 3 of the License, or
#    (at your option) any later version.
#
#    This program is distributed in the hope that it will be useful,
#    but WITHOUT ANY WARRANTY; without even the implied warranty of
#    MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
#    GNU General Public License for more details.
#
#    You should have received a copy of the GNU General Public License
#    along with this program.  If not, see <http://www.gnu.org/licenses/>.
#
##############################################################################

from osv import fields
from osv import osv
import wizard
import netsvc
import pooler
import time
from osv.orm import browse_record

date_from_to_forms = '''
<form string="Revenue">
    <group colspan="4" col="9">
        <separator string='Period' colspan="9"/>
        <field string="From" name="from_date" domain="[('project_type','in',[job_type])]" colspan="4"/>
        <field name="to_date" string="To" domain="[('project_type','in',[job_type])]" colspan="4"/>
    </group>
</form>'''

date_from_to_fields = {
        'from_date': {'string':'From', 'type':'date','required':True},
        'to_date': {'string':'To', 'type':'date','required':True}
    }


class wiz_kdvn_revenue_report(wizard.interface):
    def _open_form(self, cr, uid, data, context):
        from_date = data['form']['from_date']
        to_date = data['form']['to_date']
        
        cr.execute("Create or replace view vwcontract_for_calcualate_revenue as\
                     Select distinct project_id,contract_id from (Select project_e_id as project_id,contract_id from sale_order where state='done' \
                     union\
                     Select project_m_id as project_id,contract_id from sale_order where state='done')\
                     so left join kdvn_contract_client kcc on so.contract_id = kcc.id  where project_id is not null and contract_id is not null\
                    and registration_date between '%s' and '%s'" %(from_date,to_date))
        
        cr.execute("Create or replace view kdvn_payment_vat_invoice_for_revenue as Select \
                    kpvt.id as id,\
                    kpfc.id as payment_id,\
                    amount_e,\
                    amount_m,\
                    taxes_percent,\
                    vat_invoice_id,\
                    kpvt.amount/(1+taxes_percent/100.0) as allocated,\
                    kpvt.note\
                from\
                    kdvn_payment_from_client kpfc\
                left join\
                    kdvn_payment_vat_invoice kpvt on  kpfc.id = kpvt.payment_id\
                left join \
                    kdvn_red_invoice kri on vat_invoice_id = kri.id\
                where vat_invoice_id is not null and kri.date between '%s' and '%s'" % (from_date,to_date))
       
        cr.execute("Create or replace view kdvn_project_revenue_report as \
                        Select\
                        kp.id as id,\
                        kp.id as project_id,\
                        project_type,\
                        revenue_vn,\
                        total_budget,\
                        case when coalesce(total_budget,0)=0 then 0 else total_contract*(material_payment_vnd+site_expense_payment_vnd+sub_contractor_payment_vnd+gse_payment_vnd+kfp_payment_vnd)/total_budget end as revenue_int,\
                        material_payment_vnd,\
                        site_expense_payment_vnd,\
                        sub_contractor_payment_vnd,\
                        gse_payment_vnd,\
                        kfp_payment_vnd,\
                        profit_payment_vnd\
                        from\
                        kdvn_project kp\
                        left join\
                        (Select\
                        kp.id as project_id,\
                        case when project_type='E' then total_contract_e else total_contract_m end as total_contract,\
                        case when project_type='E' then revenue_vn_e else revenue_vn_m end as revenue_vn\
                        from \
                        kdvn_project kp\
                        left join\
                        (Select\
                        project_e_id,\
                        sum(int_amount_project_e) as total_contract_e,\
                        sum(subtotal_on_vat_invoice_e) as revenue_vn_e\
                        from\
                        (Select\
                        so.project_e_id,\
                        sum(contract_amount_vnd*case when (coalesce(contract_amount_e,0)+coalesce(contract_amount_m,0))=0 then 0 else contract_amount_e/(coalesce(contract_amount_e,0)+coalesce(contract_amount_m,0)) end) as int_amount_project_e,\
                        0 as subtotal_on_vat_invoice_e\
                        from \
                        sale_order so\
                        left join\
                        kdvn_contract_client kcc on so.contract_id = kcc.id\
                        left join\
                        vwproject_contract_received_summary vwpcrs on kcc.id = vwpcrs.id\
                        where\
                        so.state='done' and coalesce(project_e_id,0)>0 and registration_date between '%s' and '%s'\
                        group by\
                        so.project_e_id\
                        union\
                        Select \
                        so.project_e_id,\
                        0 as int_amount_project_e,\
                        sum(kpvi.amount*percent_e/(1+coalesce(kpfc.taxes_percent,0)/100.0)) as subtotal_on_vat_invoice_e\
                        from\
                        sale_order so\
                        left join\
                        kdvn_payment_from_client kpfc on so.contract_id = kpfc.contract_id\
                        left join\
                        (Select\
                        id,\
                        case when (coalesce(amount_e,0)+coalesce(amount_m,0))=0 then 0 else coalesce(amount_e)/(coalesce(amount_e,0)+coalesce(amount_m,0)) end as percent_e\
                        from\
                        kdvn_payment_from_client) vwtemp on kpfc.id = vwtemp.id\
                        left join\
                        kdvn_payment_vat_invoice kpvi on kpfc.id = payment_id\
                        left join\
                        kdvn_red_invoice kri on vat_invoice_id = kri.id\
                        where \
                        so.state='done' and coalesce(project_e_id,0)>0 and kri.date between '%s' and '%s'\
                        group by\
                        so.project_e_id) vwtemp\
                        group by project_e_id) vwrevenue_e on kp.id = vwrevenue_e.project_e_id and project_type='E'\
                        left join\
                        (Select\
                        project_m_id,\
                        sum(int_amount_project_m) as total_contract_m,\
                        sum(subtotal_on_vat_invoice_m) as revenue_vn_m\
                        from\
                        (Select\
                        so.project_m_id,\
                        sum(contract_amount_vnd*case when (coalesce(contract_amount_e,0)+coalesce(contract_amount_m,0))=0 then 0 else contract_amount_m/(coalesce(contract_amount_e,0)+coalesce(contract_amount_m,0)) end) as int_amount_project_m,\
                        0 as subtotal_on_vat_invoice_m\
                        from \
                        sale_order so\
                        left join\
                        kdvn_contract_client kcc on so.contract_id = kcc.id\
                        left join\
                        vwproject_contract_received_summary vwpcrs on kcc.id = vwpcrs.id\
                        where\
                        so.state='done' and coalesce(project_m_id,0)>0  and registration_date between '%s' and '%s'\
                        group by\
                        so.project_m_id,registration_date\
                        Union\
                        Select \
                        so.project_m_id,\
                        0 as int_amount_project_m,\
                        sum(kpvi.amount*percent_m/(1+coalesce(kpfc.taxes_percent,0)/100.0)) as subtotal_on_vat_invoice_m\
                        from\
                        sale_order so\
                        left join\
                        kdvn_payment_from_client kpfc on so.contract_id = kpfc.contract_id\
                        left join\
                        (Select\
                        id,\
                        case when (coalesce(amount_e,0)+coalesce(amount_m,0))=0 then 0 else coalesce(amount_m)/(coalesce(amount_e,0)+coalesce(amount_m,0)) end as percent_m\
                        from\
                        kdvn_payment_from_client) vwtemp on kpfc.id = vwtemp.id\
                        left join\
                        kdvn_payment_vat_invoice kpvi on kpfc.id = payment_id\
                        left join\
                        kdvn_red_invoice kri on vat_invoice_id = kri.id\
                        where \
                        so.state='done' and coalesce(project_m_id,0)>0 and kri.date between '%s' and '%s'\
                        group by\
                        so.project_m_id) vwtemp\
                        group by project_m_id) vwrevenue_m on kp.id = vwrevenue_m.project_m_id and project_type='M') vwtemp_revenue on kp.id = vwtemp_revenue.project_id\
                        left join\
                        (Select \
                        kp.id,\
                        coalesce(total_budget,0) as total_budget,\
                        sum(case when category='material' then coalesce(payment_vnd,0) else 0 end) as material_payment_vnd,\
                        sum(case when category='site_expense' then coalesce(payment_vnd,0) else 0 end) as site_expense_payment_vnd,\
                        sum(case when category='sub_contractor' then coalesce(payment_vnd,0) else 0 end) as sub_contractor_payment_vnd,\
                        sum(case when category='gse' then coalesce(payment_vnd,0) else 0 end) as gse_payment_vnd,\
                        sum(case when category='kfp' then coalesce(payment_vnd,0) else 0 end) as kfp_payment_vnd,\
                        sum(case when category='profit' then coalesce(payment_vnd,0) else 0 end) as profit_payment_vnd\
                        from\
                        kdvn_project kp\
                        left join\
                        kdvn_budget_data kbd on kp.id=kbd.project_id\
                        left join\
                        (select\
                        project_id,\
                        sum(coalesce(budget_amount,0)) as total_budget\
                        from \
                        kdvn_budget_data group by project_id) vwtemp on kbd.project_id = vwtemp.project_id\
                        left join\
                        vwbudget_category vwbc on kbd.budget_id=vwbc.budget_id\
                        left join\
                        (Select\
                        kropd.project_id,\
                        budget_id,\
                        kpri.date,    \
                        sum(fnpo_compute(fnpo_currnametoid('VND'),krop.currency_id,date_order)*(coalesce(kropd.amount,0))*vwr.rate) as payment_vnd\
                        from\
                        kdvn_request_of_payment krop left join \
                        (Select\
                        kropr.rop_id, count(kpri.id) as numof_vatinvoice,min(date) as date\
                        from\
                            kdvn_request_of_payment_kdvn_purchase_red_invoice_rel kropr\
                        left join\
                            kdvn_purchase_red_invoice kpri on kdvn_purchase_red_invoice_id = kpri.id\
                        group by rop_id) kpri on krop.id = kpri.rop_id\
                        left join\
                        purchase_order po on order_id = po.id\
                        left join\
                        vwpo_rate vwr on order_id=vwr.id\
                        left join\
                        kdvn_request_of_payment_detail kropd on krop.id = kropd.rop_id\
                        where\
                        krop.state not in ('draft','cancel') and po.state not in ('draft','cancel') and coalesce(numof_vatinvoice,0)>0\
                        Group by \
                        kropd.project_id,\
                        budget_id,\
                        kpri.date) vwtemp1 on kbd.budget_id=vwtemp1.budget_id and kbd.project_id=vwtemp1.project_id\
                        where date between '%s' and '%s'\
                        group by\
                        kp.id,total_budget) vwtemp_expense on kp.id = vwtemp_expense.id" % (from_date,to_date,from_date,to_date,from_date,to_date,from_date,to_date,from_date,to_date))
        try:
             prj_ids = ','.join(map(str,data['ids']))
        except:
             prj_ids = str(data['id'])
        if data['model']!='kdvn.project':
            domain='[]'
        elif type(eval(prj_ids))==type(2):
            domain="[('id','=',"+prj_ids+")]"
        else:
            domain="[('id','in',("+prj_ids+"))]"        
        value = {
            'domain':domain,
            'name': 'KDVN Project Revenue',
            'view_type': 'form',
            'view_mode': 'tree,form',
            'res_model': 'kdvn.project.revenue.report',
            'type': 'ir.actions.act_window',
        }
        #raise osv.except_osv("E",value)
        return value

    states = {
         'init': {
            'actions': [],
            'result': {
                'type': 'form',
                'arch':date_from_to_forms,
                'fields':date_from_to_fields,
                'state':[('open_form','Ok','gtk-ok'),('end','Cancel','gtk-cancel')]
            },
        },
        'open_form': {
            'actions': [],
            'result': {
                'type': 'action',
                'name' : 'Revenue',
                'action': _open_form,
                'state': 'end'
            },
        }, 
    }
wiz_kdvn_revenue_report('kdvn.project.revenue.open')
